package dao;

import domain.Student;
import util.JDBCUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @Author: 朱新舜
 * @Date: 2020-04-27 11:21:57
 */
public class StudentDaoDB implements StudentDao {
    private Connection conn = null;
    private PreparedStatement pstmt = null;
    private ResultSet rs = null;

    @Override
    public Student selectById(int id) {
        /*查询表中是否存在id的记录，若存在，返回Student对象，若不存在返回null*/
        try {
            conn = JDBCUtils.getConnection();
            String sql = "select * from student where id=?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, id);
            rs = pstmt.executeQuery();
            if (rs.next()) {
                int id1 = rs.getInt("id");
                String name = rs.getString("name");
                String sex = rs.getString("sex");
                int age = rs.getInt("age");
                Student student = new Student(id1, name, sex, age);
                return student;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.close(rs, pstmt, conn);
        }
        return null;
    }

    @Override
    public List<Student> selectAll() {
        ArrayList<Student> list = new ArrayList<Student>();
        try {
            conn = JDBCUtils.getConnection();
            String sql = "select * from student";
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                int id = rs.getInt("id");
                String name = rs.getString("name");
                String sex = rs.getString("sex");
                int age = rs.getInt("age");
                Student student = new Student(id, name, sex, age);
                list.add(student);
            }
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.close(rs, pstmt, conn);
        }
        return null;
    }

    @Override
    public void insert(Student student) {
        /*保存之前先判断是否存在该对象，
         * 其实数据库表中id是主键，是不允许重复的，这里不判断的话，数据库中也会自动保证id不重复*/
        Student s = selectById(student.getId());
        if (s == null) {
            try {
                conn = JDBCUtils.getConnection();
                /*这里id也让用户手动添加，所以sql于语句的写法不用写明各字段，问号的个数与字段个数一致
                 * 实际表中id是自增的，一般id不会让用户输入，insert语句需要写上插入的字段名
                 * insert into student(name,sex,age) values(?,?,?)*/
                String sql = "insert into student value(?,?,?,?)";
                pstmt = conn.prepareStatement(sql);
                pstmt.setInt(1, student.getId());
                pstmt.setString(2, student.getName());
                pstmt.setString(3, student.getSex());
                pstmt.setInt(4, student.getAge());
                int i = pstmt.executeUpdate();
                if (i > 0) {
                    System.out.println("保存成功！");
                } else {
                    System.out.println("保存失败！");
                }
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                JDBCUtils.close(pstmt, conn);
            }
        } else {
            System.out.println("保存失败！id重复了!");
        }
    }

    @Override
    public void update(int id, Student student) {
        try {
            conn = JDBCUtils.getConnection();
            String sql = "update student set name=?,sex=?,age=? where id=?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, student.getName());
            pstmt.setString(2, student.getSex());
            pstmt.setInt(3, student.getAge());
            pstmt.setInt(4, student.getId());
            int i = pstmt.executeUpdate();
            if (i > 0) {
                System.out.println("更新成功");
            } else {
                System.out.println("更新失败");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.close(pstmt, conn);
        }
    }

    @Override
    public void delete(int id) {
        Student student = selectById(id);
        if (student == null) {
            System.out.println("您要删除的学生不存在！");
        } else {
            try {
                conn = JDBCUtils.getConnection();
                String sql = "delete from student where id=?";
                pstmt = conn.prepareStatement(sql);
                pstmt.setInt(1, id);
                int i = pstmt.executeUpdate();
                if (i > 0) {
                    System.out.println("删除成功！");
                } else {
                    System.out.println("删除失败！");
                }

            } catch (SQLException e) {
                e.printStackTrace();

            } finally {
                JDBCUtils.close(pstmt, conn);
            }
        }

    }
}
